CREATE OR REPLACE FUNCTION update_ward_status() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'Occupied' THEN
        UPDATE Ward SET status='Occupied' WHERE ward_id = NEW.ward_id;
    ELSIF NEW.status = 'Available' THEN
        UPDATE Ward SET status='Available' WHERE ward_id = NEW.ward_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_appointment_conflict() RETURNS TRIGGER AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt
    FROM Appointment
    WHERE staff_id = NEW.staff_id
      AND appointment_date = NEW.appointment_date;
    IF cnt > 0 THEN
        RAISE EXCEPTION 'Doctor already has an appointment on this day!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_appointment
BEFORE INSERT ON Appointment
FOR EACH ROW
EXECUTE FUNCTION check_appointment_conflict();

CREATE OR REPLACE FUNCTION auto_invoice_amount() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO Invoice(patient_id, amount, status)
    VALUES (NEW.patient_id, 100, 'Unpaid');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_medicalrecord_invoice
AFTER INSERT ON MedicalRecord
FOR EACH ROW
EXECUTE FUNCTION auto_invoice_amount();



CREATE OR REPLACE FUNCTION get_total_invoice(p_id INT) RETURNS DECIMAL AS $$
DECLARE total DECIMAL;
BEGIN
    SELECT SUM(amount) INTO total FROM Invoice WHERE patient_id = p_id;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION staff_count_by_dept(d_id INT) RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM Staff WHERE dept_id = d_id;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION reduce_medicine_stock(m_id INT, qty INT) RETURNS VOID AS $$
BEGIN
    UPDATE Medicine SET stock = stock - qty WHERE medicine_id = m_id;
END;
$$ LANGUAGE plpgsql;
